﻿///#define MSSql
#define MySql

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

using System.IO;
using System.Text;

using System.Net;
using System.Net.Sockets;
using System.Threading;
using System.Net.Mail;

using MySql.Data.MySqlClient;

namespace ukeasbjWeb
{
    public partial class Counsellor_bonus_130926 : Bonus ///System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            // 在此处放置用户代码以初始化页面
            Common_SXD.Is_LongIn(Response, Session, Request);

            //////只允许顾问、文案、OM、总部会计、Admin 访问
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_counsellor"]);
            bool bIs_Writer = Convert.ToBoolean(Session["Is_Writer"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_G_M = Convert.ToBoolean(Session["Is_G_M"]);
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);

            if (!bIs_Counsellor && !bIs_Writer && !bIs_O_M && !bIs_Admin
                && !bIs_Accountant && !bIs_G_M )
            {
                Response.Write("<h2 align='center'><font face='宋体' color='red'>");
                Response.Write("<br>对不起，您不能使用本功能。" +
                    "<br>请用其它身份登录。");
                Response.Write("</fong></h2>");
                Response.End();

            }

            if (!IsPostBack)
            {	// 初始化页面

                //暂时关闭
                Bt_Bonus_Exel.Enabled = false;

                //////顾问、文案，设定姓名，关闭选项
                DDList_Counsellor_Writer.Enabled = true;
                DDList_Counsellor_Writer.Visible = true;
                if ((bIs_Counsellor || bIs_Writer))
                    if (!bIs_O_M)
                        if (!bIs_Admin)
                            if (!bIs_Accountant)
                            {
                                DDList_Counsellor_Writer.Enabled = false;
                                DDList_Counsellor_Writer.Visible = false;
                            }

                Init_Office_YearMonth();
                Init_CounsellorWriter();
            }
        }

//        decimal J1_Jiang, J1_Kou, J5_Jiang, J5_Kou, J7_Jiang, J7_Kou, J2_Jiang, J2_Kou, J8_Jiang, J8_Kou, J3, J4, J6, JJ1, JJ1Add_US;
//        int Max_TopPart = 0, Max_MiddlePart = 0, Max_BottomPart = 0;
        decimal J1, J2_Jiang, J2_Kou, J3, J4_Jiang, J4_Kou;
        int Max_TopPart = 0, Max_MiddlePart = 0, Max_BottomPart = 0;

        System.DateTime startDateTime20130625 =
                new System.DateTime(2013,        // Year
                6,            // Month
                25,            // Day
                0,            // Hour
                0,            // Minute
                0,            // Second
                0             // Millisecond
                );

        System.DateTime startDateTime20130525 =
            new System.DateTime(2013,        // Year
            5,            // Month
            25,            // Day
            0,            // Hour
            0,            // Minute
            0,            // Second
            0             // Millisecond
            );

        static private bool bIs_Counsellor_BJ; 
        /// <summary>
        ///  初始化：计算奖金表，计算方法，各个办公室是一样的。只有'北京'的起点不同。
        /// </summary>
        private void Init_Total_Bonus(string sTime)
        {
            ////////////////////////初始值为空
            Label_b1.Text = "";
            Label_b2.Text = "";

            Label_b3.Text = "";
            Label_b4.Text = "";

            
            ///Label_Total.Text = "";
            

            Label_Total_Bonus.Text = "";
            ///////////////////////			

            

            System.DateTime flagStartDateTime = Convert.ToDateTime(Common_SXD.strStartDate);

            //JJ1 = J1_Jiang - J1_Kou;
            
            decimal b1 = 0, b2 = 0, b3 = 0;
            string strOffice = Request["Office"].ToString().Trim();
            strOffice = Common_SXD.get_C_Office(strOffice);




            b1 = Math.Round(J1 * 0.05m * 0.167m, 2);

            b3 = Math.Round(J3 * 0.016m, 2);
           
            ///////奖2
            b2 = Math.Round((J2_Jiang - J2_Kou) * 0.016m, 2);
           
            Label_b1.Text = b1.ToString().Trim();
            Label_b2.Text = b2.ToString().Trim();

            Label_b3.Text = b3.ToString().Trim();
            
            Label_b4.Text = (J4_Jiang - J4_Kou).ToString().Trim();
            
            //Label_Total.Text = (J1_Jiang - J1_Kou + J5_Jiang - J5_Kou).ToString().Trim();

            decimal total = b1 + b2 + b3 + (J4_Jiang - J4_Kou) ;
            total = Math.Round(total, 2);
            Label_Total_Bonus.Text = total.ToString().Trim();

            
        }

        /// <summary>
        ///  初始化奖金2，顾问收费生Offer到奖
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) '含所有国别收费合同（适用于2013年6月25日以后签约，或，6月25日之前签约但为2014入学的合同）'
        /// 全程一次，全程二次，申请学校，联办
        /// </newpara>
        /// <newpara>
        /// B) '%签证'，'%文书'
        /// （适用于2013年6月25日以后签约，或6月25日之前签约但为2014入学的合同）
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus2_Jiang()
        {
            //int MyLines = 0;
            //清除表中的内容
            Table_Bonus2_Jiang.Rows.Clear();

            //J2_Jiang = 0;
            SubTotal_Lines J2_JiangReturn_Filling_Table = new SubTotal_Lines();

            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";


#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, First_Payment + Second_Payment as 'Shishoujinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, First_Payment + Second_Payment as 'Shishoujinge' from contract where ";

#endif

            string strFilter = "";
            /// <newpara>
            /// A) '含所有国别收费合同（适用于2013年6月25日以后签约，或6月25日之前签约但为2014入学的合同）'
            /// 全程一次，全程二次，申请学校，联办
            /// </newpara>
            strFilter += "((Date_Completion_Offer >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Offer <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND  ( Student_Type = '全程一次' OR Student_Type = '全程二次' OR Student_Type = '申请学校' OR Student_Type = '联办' ) ";
            strFilter += " AND Counsellor = '" + strCounsellor_Writer + "'";
            if(bIs_Counsellor_BJ)
                strFilter += " AND (Date_First_Payment > '" + startDateTime20130525.ToString().Trim() + "'  OR YearMonth_Start >= '2014-9')";
            else
                strFilter += " AND (Date_First_Payment > '" + startDateTime20130625.ToString().Trim() + "'  OR YearMonth_Start >= '2014-9')";
            
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J2_JiangReturn_Filling_Table = Filling_Table_with_Heads(sSql + strFilter, J2_JiangReturn_Filling_Table, 1, Table_Bonus2_Jiang);

            
            ///////////////////////////////////////////////////////
            sSql = "";

#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, First_Payment + Second_Payment as 'Shishoujinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, First_Payment + Second_Payment - Termination_Refund  as 'Shishoujinge' from contract where ";

#endif
            strFilter = "";
            /// <newpara>
            /// B) '%签证'，'%文书'
            /// （适用于2013年6月25日以后签约，或6月25日之前签约但为2014入学的合同）
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ( Student_Type like '%签证' OR Student_Type like '%文书' ) ";
            strFilter += " AND ( First_payment + Second_payment - Termination_Refund) > 0 "; 
            strFilter += " AND Counsellor = '" + strCounsellor_Writer + "'";
            
            if(bIs_Counsellor_BJ)
                strFilter += " AND (Date_First_Payment > '" + startDateTime20130525.ToString().Trim() + "'  OR YearMonth_Start >= '2014-9')";
            else
                strFilter += " AND (Date_First_Payment > '" + startDateTime20130625.ToString().Trim() + "'  OR YearMonth_Start >= '2014-9')";
            
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            ///////////////////////////////////////////////////////////////////

            J2_JiangReturn_Filling_Table = Filling_Table(sSql + strFilter, J2_JiangReturn_Filling_Table, 1, Table_Bonus2_Jiang);
            

            J2_Jiang = J2_JiangReturn_Filling_Table.Sub_Total;
            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J2_Jiang.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus2_Jiang.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            //if (MyLines > Max_TopPart)
            //    Max_TopPart = MyLines;

            if (J2_JiangReturn_Filling_Table.Lines > Max_TopPart)
                Max_TopPart = J2_JiangReturn_Filling_Table.Lines;
        }

        /// <summary>
        ///  初始化奖金2扣, 文案收费生Offer/Visa退约
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) '全程一次'，'申请学校'，offer退约。
        ///    2011年7月26日之前
        /// </newpara>
        /// <newpara>
        /// AA) '全程一次'，'申请学校'，offer退约。
        ///    2011年7月26日之后
        /// </newpara>
        /// <newpara>
        /// B) '全程二次'的 offer 完成退约（第二期合同没有执行）。
        ///    2011年7月26日之前
        /// </newpara>
        /// <newpara>
        /// BB) '全程二次'的 offer 完成退约（第二期合同没有执行）。
        ///    2011年7月26日之后
        /// </newpara>
        /// <newpara>
        /// C) 联办（不含美）退款。退款金额 ，文案扣奖，
        ///    2011年7月26日之前
        /// </newpara>
        /// CC) 联办（不含美）退款。退款金额 ，文案扣奖，
        ///    2011年7月26日之后
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus2_Kou()
        {
            //int MyLines = 0;
            //清除表中的内容
            Table_Bonus2_Kou.Rows.Clear();

            //J2_Kou = 0;
            SubTotal_Lines J2_KouFilling_Table = new SubTotal_Lines();

            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";

#endif

            string strFilter = "";
            /// <newpara>
            /// A) '全程一次'，'申请学校'，offer退约。
            ///    2011年7月26日之前
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Date_Completion_Offer > '1930-1-1' AND Destinations_OS not like '美国' AND ( Student_Type = '全程一次' OR  Student_Type = '申请学校' )  AND Counsellor = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";
            strFilter += "AND ( Date_Termination_Refund < '2011-7-26' AND Date_Finished_Termination_Refund < '2011-7-26') ";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J2_KouFilling_Table = Filling_Table_with_Heads(sSql + strFilter, J2_KouFilling_Table, 1, Table_Bonus2_Kou);

            sSql = "";

#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";

#endif
            strFilter = "";
            /// <newpara>
            /// AA) '全程一次'，'申请学校'，offer退约。
            ///    2011年7月26日之后
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Finished_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Finished_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Date_Completion_Offer > '1930-1-1' AND Destinations_OS not like '美国' AND ( Student_Type = '全程一次' OR  Student_Type = '申请学校' )  AND Counsellor = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";
            strFilter += "AND ( Date_Termination_Refund >= '2011-7-26' OR Date_Finished_Termination_Refund >= '2011-7-26') ";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J2_KouFilling_Table = Filling_Table(sSql + strFilter, J2_KouFilling_Table, 1, Table_Bonus2_Kou);

            ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";

#endif
            strFilter = "";
            /// <newpara>
            /// B) '全程二次'的 offer 完成退约。
            ///    2011年7月26日之前
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Date_Completion_Offer > '1930-1-1' AND Destinations_OS not like '美国' AND ( Student_Type = '全程二次' )  AND Writer = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";
            strFilter += "AND ( Date_Termination_Refund < '2011-7-26' AND Date_Finished_Termination_Refund < '2011-7-26') ";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J2_KouFilling_Table = Filling_Table(sSql + strFilter, J2_KouFilling_Table, 1, Table_Bonus2_Kou);



#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";

#endif
            strFilter = "";
            /// <newpara>
            /// BB) '全程二次'的 offer 完成退约。
            ///    2011年7月26日之后
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Finished_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Finished_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "'))  AND Date_Completion_Offer > '1930-1-1' AND Destinations_OS not like '美国' AND ( Student_Type = '全程二次' )  AND Writer = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";
            strFilter += "AND ( Date_Termination_Refund >= '2011-7-26' OR Date_Finished_Termination_Refund >= '2011-7-26') ";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J2_KouFilling_Table = Filling_Table(sSql + strFilter, J2_KouFilling_Table, 1, Table_Bonus2_Kou);


#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, (Termination_Refund - First_Payment - Second_Payment + 12000) as 'Shituijinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, (Termination_Refund - First_Payment - Second_Payment + 12000) as 'Shituijinge' from contract where ";

#endif
            strFilter = "";
            /// <newpara>
            /// C) 联办（不含美）退款。退款金额 ，文案扣奖，Offer退约。
            ///    2011年7月26日之前
            /// </newpara>
            strFilter += "((Date_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Date_Completion_Offer > '1930-1-1' AND ( Student_Type like '%联办' AND Destinations_OS not like '%美%' ) AND Writer = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund >  First_Payment + Second_Payment - 12000 ";
            strFilter += "AND ( Date_Termination_Refund < '2011-7-26' AND Date_Finished_Termination_Refund < '2011-7-26') ";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J2_KouFilling_Table = Filling_Table(sSql + strFilter, J2_KouFilling_Table, 1, Table_Bonus2_Kou);


#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, (Termination_Refund - First_Payment - Second_Payment + 12000) as 'Shituijinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, (Termination_Refund - First_Payment - Second_Payment + 12000) as 'Shituijinge' from contract where ";

#endif
            strFilter = "";
            /// <newpara>
            /// CC) 联办（不含美）退款。退款金额 ，文案扣奖，Offer退约。
            ///    2011年7月26日之后
            /// </newpara>
            strFilter += "((Date_Finished_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Finished_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Date_Completion_Offer > '1930-1-1' AND ( Student_Type like '%联办' AND Destinations_OS not like '%美%' ) AND Writer = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund >  First_Payment + Second_Payment - 12000 ";
            strFilter += "AND ( Date_Termination_Refund >= '2011-7-26' OR Date_Finished_Termination_Refund >= '2011-7-26') ";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J2_KouFilling_Table = Filling_Table(sSql + strFilter, J2_KouFilling_Table, 1, Table_Bonus2_Kou);

            J2_Kou = J2_KouFilling_Table.Sub_Total;
            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J2_Kou.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus2_Kou.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (J2_KouFilling_Table.Lines > Max_TopPart)
                Max_TopPart = J2_KouFilling_Table.Lines;

        }

        /// <summary>
        ///  初始化奖金表1，老方案顾问结案奖
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 顾问结案奖，4种合同，净收入（一期款 +  二期款 - 退款） 为结案的金额
        /// </newpara>
        /// <newpara>
        /// B) 顾问结案奖，4种合同，净收入（一期款 - 退款） 为结案的金额
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus1()
        {

            //清除表中的内容
            Table_Bonus1.Rows.Clear();
            SubTotal_Lines J1Filling_Table = new SubTotal_Lines();

            //生成 sql 命令串

            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";


#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";

#endif

            string strFilter = "";
            /// <newpara>
            /// A) 顾问结案奖，4种合同，净收入（一期款 +  二期款 - 退款） 为结案的金额
            /// </newpara>
            strFilter += "(Date_first_payment >= '1930-1-1') AND (Date_first_payment <= Date_Completion_Contract )";
            strFilter += " AND (Date_second_payment >= '1930-1-1') AND (Date_second_payment <= Date_Completion_Contract ) AND";
            strFilter += " ( First_payment + Second_payment - Termination_Refund) > 0 AND";

            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ( ( Student_Type like '全程%' AND Destinations_OS not like '美国' /*AND Destinations_OS not like '加拿大'*/ ) OR  Student_Type like '%联办' OR ( Student_Type like '申请学校' AND Destinations_OS not like '美国' /*AND Destinations_OS not like '加拿大'*/ ) OR  Student_Type like '%签证' OR Student_Type like '%文书' )  AND Counsellor = '" + strCounsellor_Writer + "'";
            
            if(bIs_Counsellor_BJ)
                strFilter += " AND (Date_First_Payment <= '" + startDateTime20130525.ToString().Trim() + "'  AND YearMonth_Start like '2013-9') ";
            else
                strFilter += " AND (Date_First_Payment <= '" + startDateTime20130625.ToString().Trim() + "'  AND YearMonth_Start like '2013-9') ";
            
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J1Filling_Table = Filling_Table_with_Heads(sSql + strFilter, J1Filling_Table, 1, Table_Bonus1);


            //生成 sql 命令串
            sSql = "";

#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, First_Payment - Termination_Refund as 'Shishoujinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, First_Payment - Termination_Refund as 'Shishoujinge' from contract where ";

#endif

            strFilter = "";
            /// <newpara>
            /// B) 顾问结案奖，4种合同，净收入（一期款 - 退款） 为结案的金额
            /// </newpara>
            strFilter += "(Date_first_payment >= '1930-1-1') AND (Date_first_payment <= Date_Completion_Contract )";
            strFilter += " AND (Date_second_payment < '1930-1-1') AND ";
            strFilter += " ( First_payment + Second_payment - Termination_Refund) > 0 AND";

            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ( ( Student_Type like '全程%' AND Destinations_OS not like '美国' /*AND Destinations_OS not like '加拿大'*/ ) OR  Student_Type like '%联办' OR ( Student_Type like '申请学校' AND Destinations_OS not like '美国' /*AND Destinations_OS not like '加拿大'*/ ) OR  Student_Type like '%签证' OR Student_Type like '%文书'  )  AND Counsellor = '" + strCounsellor_Writer + "'";
            
            if(bIs_Counsellor_BJ)
                strFilter += " AND (Date_First_Payment <= '" + startDateTime20130525.ToString().Trim() + "'  AND YearMonth_Start like '2013-9') ";
            else
                strFilter += " AND (Date_First_Payment <= '" + startDateTime20130625.ToString().Trim() + "'  AND YearMonth_Start like '2013-9') ";
            
            if(bIs_Counsellor_BJ)
                strFilter += " AND (Date_First_Payment <= '" + startDateTime20130525.ToString().Trim() + "'  AND YearMonth_Start like '2013-9')";
            else
                strFilter += " AND (Date_First_Payment <= '" + startDateTime20130625.ToString().Trim() + "'  AND YearMonth_Start like '2013-9')";
            
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J1Filling_Table = Filling_Table(sSql + strFilter, J1Filling_Table, 1, Table_Bonus1);


            J1 = J1Filling_Table.Sub_Total;

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J1.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus1.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }

        /// <summary>
        ///  初始化奖金表3，顾问结案奖
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 顾问结案奖，4种合同，净收入（一期款 +  二期款 - 退款） 为结案的金额
        /// </newpara>
        /// <newpara>
        /// B) 顾问结案奖，4种合同，净收入（一期款 - 退款） 为结案的金额
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus3()
        {

            //清除表中的内容
            Table_Bonus3.Rows.Clear();
            SubTotal_Lines J3Filling_Table = new SubTotal_Lines();

            //生成 sql 命令串

            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";


#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";

#endif

            string strFilter = "";
            /// <newpara>
            /// A) 顾问结案奖，4种合同，净收入（一期款 +  二期款 - 退款） 为结案的金额
            /// </newpara>
            strFilter += " Date_first_payment >= '1930-1-1' AND Date_first_payment <= Date_Completion_Contract ";
            strFilter += " AND Date_second_payment >= '1930-1-1' AND Date_second_payment <= Date_Completion_Contract ";
            strFilter += " AND First_payment + Second_payment - Termination_Refund > 0 ";

            strFilter += " AND Date_Completion_Contract >= '" + Common_SXD.strStartDate + "' AND Date_Completion_Contract <= '" + Common_SXD.strEndingDate + "'";
            strFilter += " AND ( Student_Type like '全程%'  OR  Student_Type like '%联办' OR Student_Type like '申请学校' OR  Student_Type like '%签证' OR Student_Type like '%文书' )  AND Counsellor = '" + strCounsellor_Writer + "'";
            
            if(bIs_Counsellor_BJ)
                strFilter += " AND (Date_First_Payment > '" + startDateTime20130525.ToString().Trim() + "'  OR YearMonth_Start >= '2014-9') ";
            else
                strFilter += " AND (Date_First_Payment > '" + startDateTime20130625.ToString().Trim() + "'  OR YearMonth_Start >= '2014-9') ";
            
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J3Filling_Table = Filling_Table_with_Heads(sSql + strFilter, J3Filling_Table, 1, Table_Bonus3);
            
            //生成 sql 命令串
            sSql = "";

#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, First_Payment - Termination_Refund as 'Shishoujinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, First_Payment - Termination_Refund as 'Shishoujinge' from contract where ";

#endif

            strFilter = "";
            /// <newpara>
            /// B) 顾问结案奖，4种合同，净收入（一期款 - 退款） 为结案的金额
            /// </newpara>
            strFilter += " Date_first_payment >= '1930-1-1' AND Date_first_payment <= Date_Completion_Contract ";
            strFilter += " AND Date_second_payment < '1930-1-1' ";
            strFilter += " AND First_payment + Second_payment - Termination_Refund > 0 ";

            strFilter += " AND Date_Completion_Contract >= '" + Common_SXD.strStartDate + "' AND Date_Completion_Contract <= '" + Common_SXD.strEndingDate + "'";
            strFilter += " AND ( Student_Type like '全程%' OR  Student_Type like '%联办' OR Student_Type like '申请学校' OR Student_Type like '%签证' OR Student_Type like '%文书' ) AND Counsellor = '" + strCounsellor_Writer + "'";
            
            if(bIs_Counsellor_BJ)
                strFilter += " AND (Date_First_Payment > '" + startDateTime20130525.ToString().Trim() + "'  OR YearMonth_Start >= '2014-9')";
            else
                strFilter += " AND (Date_First_Payment > '" + startDateTime20130625.ToString().Trim() + "'  OR YearMonth_Start >= '2014-9')";
            
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J3Filling_Table = Filling_Table(sSql + strFilter, J3Filling_Table, 1, Table_Bonus3);
            

            J3 = J3Filling_Table.Sub_Total;

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J3.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus3.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }

        /// <summary>
        ///  初始化奖金4扣奖，顾问零中介退约
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 本月发生的零中介退款，需要扣奖的合同。
        ///    2011年7月26日之前
        /// </newpara>
        /// <newpara>
        /// AA) 本月发生的零中介退款，需要扣奖的合同。
        ///    2011年7月26日之后
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus4_Kou()
        {

            //清除表中的内容
            Table_Bonus4_Kou.Rows.Clear();
            ///J7_Kou = 0;
            SubTotal_Lines J4_KouFilling_Table = new SubTotal_Lines();

            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";
            sSql = "select  Student_Name, 50  as 'Koukuan' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) 本月发生的零中介退款，需要扣奖的合同。
            ///    2011年7月26日之前
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "( Date_Termination_Refund >= '" + Common_SXD.strStartDate + "' AND  Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "' ) AND Termination_Refund<>0 AND (Notes like '%deduct%' ) AND (First_Payment + Second_Payment) >= 1000  AND Student_Type = '零中介'  AND Counsellor = '" + strCounsellor_Writer + "'";
            strFilter += "AND ( Date_Termination_Refund < '2011-7-26' AND Date_Finished_Termination_Refund < '2011-7-26')  ";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J4_KouFilling_Table = Filling_Table_with_Heads(sSql + strFilter, J4_KouFilling_Table, 1, Table_Bonus4_Kou);


            //string strOffice = Request["Office"].ToString().Trim();

            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            sSql = "";
            sSql = "select  Student_Name, 50  as 'Koukuan' from contract where ";
            strFilter = "";
            /// <newpara>
            /// AA) 本月发生的零中介退款，需要扣奖的合同。
            ///    2011年7月26日之后
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "( Date_Finished_Termination_Refund >= '" + Common_SXD.strStartDate + "' AND  Date_Finished_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "' ) AND Termination_Refund<>0 AND (Notes like '%deduct%' ) AND (First_Payment + Second_Payment) >= 1000  AND Student_Type = '零中介'  AND Counsellor = '" + strCounsellor_Writer + "'";
            strFilter += "AND ( Date_Termination_Refund >= '2011-7-26' OR Date_Finished_Termination_Refund >= '2011-7-26' ) ";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J4_KouFilling_Table = Filling_Table(sSql + strFilter, J4_KouFilling_Table, 1, Table_Bonus4_Kou);

            J4_Kou = J4_KouFilling_Table.Sub_Total;
            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                //                HtmlTableCell cell = new HtmlTableCell();
                //                cell.Controls.Add(new LiteralControl(" "));
                //                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);


                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J4_Kou.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus4_Kou.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }



        /// <summary>
        ///  初始化奖金奖4，顾问零中介签约奖。
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 本月，第一次付款发生在本月， First_Payment大于等于1000
        /// </newpara>
        /// <newpara>
        /// B) 本月，发生第二次付款， 第一次付款小于1000, 2次款之和大于等于1000。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus4_Jiang()
        {

            //清除表中的内容
            Table_Bonus4_Jiang.Rows.Clear();
            
            SubTotal_Lines J4_JiangFilling_Table = new SubTotal_Lines();

            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";
            sSql = "select  Student_Name, First_Payment + Second_Payment as 'Yajing', 50 as 'Bonus'  from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) 本月，第一次付款发生在本月， First_Payment >=1000
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_first_payment >= '" + Common_SXD.strStartDate + "') AND (Date_first_payment <= '" + Common_SXD.strEndingDate + "'))  AND First_Payment >= 1000 AND Student_Type = '零中介'  AND counsellor = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J4_JiangFilling_Table = Filling_Table_with_Heads(sSql + strFilter, J4_JiangFilling_Table, 1, Table_Bonus4_Jiang);


            sSql = "select  Student_Name, First_Payment + Second_Payment as 'Yajing', 50 as 'Bonus'  from contract where ";

            /// <newpara>
            /// B) 考虑第二次付款发生在本月， 第一次付款小于1000, 2次款之和大于等于1000。
            /// </newpara>
            strFilter = "";
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Second_payment >= '" + Common_SXD.strStartDate + "') AND (Date_Second_payment <= '" + Common_SXD.strEndingDate + "')) AND First_Payment < 1000 AND First_Payment + Second_Payment >= 1000   AND Student_Type = '零中介'  AND counsellor = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J4_JiangFilling_Table = Filling_Table(sSql + strFilter, J4_JiangFilling_Table, 1, Table_Bonus4_Jiang);

            J4_Jiang = J4_JiangFilling_Table.Sub_Total;
            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J4_Jiang.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus4_Jiang.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }

        /// <summary>
        /// 顾问、文案控件的初始化
        /// </summary>
        private void Init_CounsellorWriter()
        {
            string sDDList_Office = Request["Office"].ToString().Trim();
            sDDList_Office = Common_SXD.get_C_Office(sDDList_Office);

            // 载入顾问、文案数据，绑定到 DDList_Counsellor_Writer 中
            string sSql = "select * from Employees ";
            sSql += "where C_N_Employee like '?' or ( ( Is_Counsellor = 1 or  Is_Writer = 1 ) and Is_Active = 1 and Office ='" + sDDList_Office + "' ) ";
            sSql += "order by Code_Employee asc";
            
            Common_SXD.Init_DropDownList(DDList_Counsellor_Writer, "Employees", sSql);

        }

        /// <summary>
        /// 地区、计奖年、月的初始化
        /// </summary>
        private void Init_Office_YearMonth()
        {
            TextBox_Year_Month.Text = Common_SXD.TimeToYM(DateTime.Now.ToString());
        }

        protected void Bt_Refress_Search_Click(object sender, EventArgs e)
        {

            Common_SXD.Kill_Sleep_Threads(Response);
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            //// 设定变量:bIs_Counsellor_BJ, 确定是否为北京办公室的顾问、文案
            string tempCounsellor = Common_SXD.CHAR_MySQL("select Office from Employees ", "C_N_Employee like '" + strCounsellor_Writer + "'", Response);
            if (tempCounsellor.Trim() == "北京")
                bIs_Counsellor_BJ = true;
            else
                bIs_Counsellor_BJ = false;

            // 填写奖金计算网页
            Common_SXD.Set_BonusDate(TextBox_Year_Month.Text, false);
            //Init_Tables_Bonus1_Jiang();
            Init_Tables_Bonus1();
            Init_Tables_Bonus2_Jiang();
            Init_Tables_Bonus2_Kou();
            Init_Tables_Bonus3();
            
            Init_Tables_Bonus4_Jiang();
            Init_Tables_Bonus4_Kou();
            
            int tMonth = DateTime.Now.Month;
            int tYear = DateTime.Now.Year;
            Init_Total_Bonus(TextBox_Year_Month.Text);

            string strOffice = Request["Office"].ToString().Trim();
            strOffice = Common_SXD.get_C_Office(strOffice);
            /*
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();
            */
            
            System.DateTime bonusDateTime20101201 =
    new System.DateTime(2010,        // Year
    12,            // Month
    1,            // Day
    0,            // Hour
    0,            // Minute
    0,            // Second
    0             // Millisecond
    );

            string temp = TextBox_Year_Month.Text + "-1";
            System.DateTime bonusNow = Convert.ToDateTime(temp);
            /// excel 奖金表的行数限制，分为上中下3个最大行数。
            if (bonusNow >= bonusDateTime20101201 && Max_TopPart <= 40 && Max_MiddlePart <= 16
                && Max_BottomPart <= 20)
            {
                Bt_Bonus_Exel.Enabled = true; //允许发送 excel格式的(101217版)奖金表
                /*  注释后不生成奖金 execl 文件
                SynchronousSocketClient MyClient = new SynchronousSocketClient();
                // 生成 exel 奖金文件
                MyClient.StartClient(strOffice, strCounsellor_Writer, Common_SXD.tempYear, Common_SXD.tempMonth, Response);
            
                 */
            }
            else
            {
                Bt_Bonus_Exel.Enabled = false;
                Response.Write("无法生成 excel 格式的(101217版)奖金表（日期不合适或者数据超出表格范围）！");

            }

        }

        protected void Bt_Bonus_Exel_Click(object sender, EventArgs e)
        {

        }
    }
}